﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using SqlSugar;
using Sunny.UI;
using 数据迁移工具.Factory;
using DbType = SqlSugar.DbType;

namespace 数据迁移工具.Common.DbContext
{
    public class OracleDbContext : SugarDbContext
    {
        #region 字段 / 构造
        private readonly CancellationToken ct = new();
        private readonly SemaphoreSlim semaphore = new SemaphoreSlim(5);

        private static readonly Dictionary<string, MatchEvaluator> OracleToMySqlFuncMapping = new();

        static OracleDbContext()
        {
            // 1. NVL(col, val)  -> IFNULL(col, val)
            string p1 = @"NVL\s*\((?<col>.*?)\s*,\s*(?<val>.*?)\)";
            OracleToMySqlFuncMapping.Add(p1, m =>
            {
                var col = m.Groups["col"].Value.Trim();
                var val = m.Groups["val"].Value.Trim();
                return $"IFNULL({col}, {val})";
            });

            // 2. SYSDATE -> NOW()
            string p2 = @"SYSDATE";
            OracleToMySqlFuncMapping.Add(p2, _ => "NOW()");

            // 3. TO_CHAR(dateCol, 'YYYY-MM-DD') -> DATE_FORMAT(dateCol, '%Y-%m-%d')
            string p3 = @"TO_CHAR\s*\(\s*(?<col>.+?)\s*,\s*'(?<fmt>[^']+)'\s*\)";
            OracleToMySqlFuncMapping.Add(p3, m =>
            {
                var col = m.Groups["col"].Value.Trim();
                var fmt = m.Groups["fmt"].Value
                                .Replace("YYYY", "%Y")
                                .Replace("MM", "%m")
                                .Replace("DD", "%d")
                                .Replace("HH24", "%H")
                                .Replace("MI", "%i")
                                .Replace("SS", "%s");
                return $"DATE_FORMAT({col}, '{fmt}')";
            });

            // 4. ROWNUM <= N  -> LIMIT N
            string p4 = @"ROWNUM\s*<=\s*(?<val>\d+)";
            OracleToMySqlFuncMapping.Add(p4, m => $"LIMIT {m.Groups["val"].Value}");
        }

        public OracleDbContext(ConnectionConfig config) : base(config) { }
        #endregion

        #region 获取用户表信息
        private const string tbColInfoSql = @"SELECT DISTINCT 
                            t1.table_name AS ""TableName"",
                            t6.comments AS ""TbComment"",
                            t1.column_id AS ""ColumnId"",
                            t1.column_name AS ""DbColumnName"",
                            t5.comments AS ""ColComment"",
                            t1.data_type AS ""DataType"",     
                            t1.data_length AS ""Length"", 
                            t1.data_precision AS ""Precision"",
                            t1.data_scale AS ""Scale"",
                            t1.nullable AS ""Nullable"",
                            CASE WHEN pk.column_name IS NOT NULL THEN 'YES' ELSE 'NO' END AS ""IsPrimaryKey""
                        FROM user_tab_columns t1   
                        LEFT JOIN (
                            SELECT t2.table_name,     
                                   t2.column_name,  
                                   t2.column_position,  
                                   t2.descend,      
                                   t3.index_name        
                            FROM user_ind_columns t2   
                            LEFT JOIN user_indexes t3   
                                ON t2.table_name = t3.table_name 
                               AND t2.index_name = t3.index_name
                               AND t3.status = 'VALID' 
                               AND t3.uniqueness = 'UNIQUE'
                        ) t4  
                            ON t1.table_name = t4.table_name 
                           AND t1.column_name = t4.column_name 
                        LEFT JOIN user_col_comments t5 
                            ON t1.table_name = t5.table_name 
                           AND t1.column_name = t5.column_name 
                        LEFT JOIN user_tab_comments t6 
                            ON t1.table_name = t6.table_name
                        LEFT JOIN (
                            SELECT ucc.table_name, ucc.column_name
                            FROM user_cons_columns ucc
                            JOIN user_constraints uc 
                              ON ucc.constraint_name = uc.constraint_name
                             AND uc.constraint_type = 'P' -- 主键
                        ) pk
                            ON t1.table_name = pk.table_name 
                           AND t1.column_name = pk.column_name
                        ORDER BY t1.table_name, t1.column_id;";

        private const string tbCount = "SELECT COUNT(table_name) FROM user_tables";
        #endregion

        #region 元数据查询
        /// <summary>
        /// 获取 Oracle 所有表信息
        /// </summary>
        public override List<TableInfo> GetTableInfos(string dbName)
        {
            string sql = $"SELECT OWNER AS DbName, TABLE_NAME AS TbName, 'TABLE' AS TbType FROM ALL_TABLES WHERE OWNER = '{dbName}'";
            return DB.Ado.SqlQuery<TableInfo>(sql);
        }

        /// <summary>
        /// 获取 Oracle 视图定义
        /// </summary>
        protected override List<ViewDefinition> GetViewDefinitions(string owner)
        {
            string sql = $"SELECT OWNER AS TABLE_NAME, VIEW_NAME, TEXT AS VIEW_DEFINITION FROM ALL_VIEWS WHERE OWNER = '{owner}'";
            return DB.Ado.SqlQuery<ViewDefinition>(sql);
        }

        #endregion

        #region 实体类生成
        /// <summary>
        /// 根据表结构生成实体类
        /// </summary>
        /// <param name="configId"></param>
        /// <param name="namesp"></param>
        public override void GenerateModels(string configId, string modelPath, List<string> tables, string ignorePrefix = "", 
            string namesp = "Default.Models", Action<int> processMonitor = null, Action<string> processLog = null)
        {
            if (!Directory.Exists(modelPath))
                Directory.CreateDirectory(modelPath);
            int total = DB.Ado.GetInt(tbCount);
            List<RowColumnInfo> rows = DB.SqlQueryable<RowColumnInfo>(tbColInfoSql).ToList();
            // 读取模板
            string tempTxt = Directory.GetCurrentDirectory() + "\\Model\\ModelTemplate\\Template.txt";
            string template = File.ReadAllText(tempTxt);

            int current = 0;
            tables = rows.Select(row => row.TableName).Distinct<string>().ToList();
            tables.ForEach(tb =>
            {
                string tbName = tb; // 临时变量解决闭包问题
                Task.Run(() =>
                {
                    try
                    {
                        semaphore.Wait();
                        // 类名以及文件名处理
                        string className = string.IsNullOrWhiteSpace(ignorePrefix) ? tb : tb.Replace(ignorePrefix, "");
                        string fileName = $"{modelPath}\\{className}.cs";
                        bool flag = true;
                        string tbComment = string.Empty;
                        StringBuilder columnBuilder = new StringBuilder();
                        rows
                        .Where(row => row.TableName == tb)
                        .Distinct()
                        .ForEach(row =>
                        {
                            if (flag)
                            {
                                tbComment = row.TbComment;
                                flag = false;
                            }

                            string cType = OracleToCsharpTypeConvert(row);
                            bool nullable = row.Nullable == "Y";
                            if (cType != "string")
                                cType = nullable ? cType + "?" : cType;
                            row.ColComment = row.ColComment?.Replace("\r\n", " ").Replace("\n", " ") ?? "";
                            columnBuilder.Append($@" 
        /// <summary>
        /// Desc: {row.ColComment}
        /// Default: {row.Default}
        /// Nullable: {row.Nullable}
        /// </summary>");
                            if (row.IsPrimaryKey == "YES")
                            {
                                columnBuilder.AppendLine($"\n        [SugarColumn(IsPrimaryKey = true, ColumnName = \"{row.DbColumnName}\", ColumnDescription = \"{row.ColComment}\", Length = {row.Length},IsNullable = {nullable.ToString().ToLower()})]");
                            }
                            else
                            {
                                if (cType.Contains("decimal"))
                                    columnBuilder.AppendLine($"\n        [SugarColumn(ColumnName = \"{row.DbColumnName}\", ColumnDescription = \"{row.ColComment}\", Length = {row.Length},DecimalDigits = {row.Scale},IsNullable = {nullable.ToString().ToLower()})]");
                                else if (cType.Contains("DateTime"))
                                    columnBuilder.AppendLine($"\n        [SugarColumn(ColumnName = \"{row.DbColumnName}\", ColumnDescription = \"{row.ColComment}\",IsNullable = {nullable.ToString().ToLower()})]");
                                else
                                    columnBuilder.AppendLine($"\n        [SugarColumn(ColumnName = \"{row.DbColumnName}\", ColumnDescription = \"{row.ColComment}\", Length = {row.Length},IsNullable = {nullable.ToString().ToLower()})]");
                            }
                            columnBuilder.AppendLine($"        public {cType} {row.DbColumnName} " + "{ get; set; }");

                            string logInfo = $"正在生成表： {row.TableName}, 对应的实体类属性：{cType} {row.DbColumnName} ...";
                            processLog?.Invoke(logInfo);
                        });

                        string tempResult = template.Replace("$namespace", namesp)
                                .Replace("$Comment", tbComment)
                                .Replace("$TableName", tb)
                                .Replace("$ClassName", className)
                                .Replace("$ColumnStr", columnBuilder.ToString());
                        File.WriteAllText(fileName, tempResult);

                        current++;
                        if (processMonitor != null)
                        {
                            double res = Math.Round(1.0 * current / total, 2) * 100;
                            processMonitor.Invoke((int)res);
                        }
                    }
                    finally
                    {
                        semaphore.Release();
                    }
                });
            });
        }

        /// <summary>
        /// 生成临时 Model 实体类
        /// </summary>
        /// <param name="configId"></param>
        /// <param name="modelPath"></param>
        /// <param name="tables"></param>
        /// <param name="ignorePrefix"></param>
        /// <param name="namesp"></param>
        /// <param name="processMonitor"></param>
        /// <param name="processLog"></param>
        public override void GenerateTmpModels(string configId, string modelPath, List<string> tables, string ignorePrefix = "",
            string namesp = "Default.Models", Action<string> processLog = null)
        {
            if (!Directory.Exists(modelPath))
                Directory.CreateDirectory(modelPath);
            List<RowColumnInfo> rows = DB.SqlQueryable<RowColumnInfo>(tbColInfoSql).ToList();
            // 读取模板
            string tempTxt = Directory.GetCurrentDirectory() + "\\Model\\ModelTemplate\\Template.txt";
            string template = File.ReadAllText(tempTxt);

            tables = rows.Select(row => row.TableName).Distinct<string>().ToList();

            List<Task> tasks = new List<Task>();
            tables.ForEach(tb =>
            {
                string tbName = tb; // 临时变量解决闭包问题
                Task task = Task.Run(() =>
                {
                    try
                    {
                        semaphore.Wait();
                        // 类名以及文件名处理
                        string className = string.IsNullOrWhiteSpace(ignorePrefix) ? tb : tb.Replace(ignorePrefix, "");
                        string fileName = $"{modelPath}\\{className}.cs";
                        bool flag = true;
                        string tbComment = string.Empty;
                        StringBuilder columnBuilder = new StringBuilder();
                        rows
                        .Where(row => row.TableName == tb)
                        .Distinct()
                        .ForEach(row =>
                        {
                            if (flag)
                            {
                                tbComment = row.TbComment;
                                flag = false;
                            }

                            string cType = OracleToCsharpTypeConvert(row);
                            bool nullable = row.Nullable == "Y";
                            if (cType != "string")
                                cType = nullable ? cType + "?" : cType;
                            row.ColComment = row.ColComment?.Replace("?","").Replace("？","").Replace("\r\n", " ").Replace("\n", " ") ?? "";
                            columnBuilder.Append($@" 
        /// <summary>
        /// Desc: {row.ColComment}
        /// Default: {row.Default}
        /// Nullable: {row.Nullable}
        /// </summary>");

                            List<string> noLengthTypes = new List<string>() { "DateTime", "byte[]", "DateTime?", "byte[]?" };
                            if (row.IsPrimaryKey == "YES")
                            {
                                columnBuilder.AppendLine($"\n        [SugarColumn(IsPrimaryKey = true, ColumnName = \"{row.DbColumnName}\", Length = {row.Length},IsNullable = {nullable.ToString().ToLower()})]");
                            }
                            else
                            {
                                if (cType.Contains("decimal"))
                                    columnBuilder.AppendLine($"\n        [SugarColumn(ColumnName = \"{row.DbColumnName}\", Length = {row.Length},DecimalDigits = {row.Scale},IsNullable = {nullable.ToString().ToLower()})]");
                                else if (noLengthTypes.Contains(cType))
                                    columnBuilder.AppendLine($"\n        [SugarColumn(ColumnName = \"{row.DbColumnName}\", IsNullable = {nullable.ToString().ToLower()})]");
                                else
                                    columnBuilder.AppendLine($"\n        [SugarColumn(ColumnName = \"{row.DbColumnName}\", Length = {row.Length},IsNullable = {nullable.ToString().ToLower()})]");

                            }
                            columnBuilder.AppendLine($"        public {cType} {row.DbColumnName} " + "{ get; set; }");

                            string logInfo = $"正在转换表： {row.TableName}, 对应的列名：{row.DataType} {row.DbColumnName} ...";
                            processLog?.Invoke(logInfo);
                        });

                        string tempResult = template.Replace("$namespace", namesp)
                                .Replace("$Comment", tbComment)
                                .Replace("$TableName", tb)
                                .Replace("$ClassName", className)
                                .Replace("$ColumnStr", columnBuilder.ToString());
                        File.WriteAllText(fileName, tempResult);
                    }
                    finally
                    {
                        semaphore.Release();
                    }
                });
                tasks.Add(task);
            });

            Task.WaitAll(tasks.ToArray());
        }
        #endregion

        #region 视图转换
        public override void ViewConvert(string srcMode, DbType dstType, string targetId, string dstDbName)
        {
            List<ViewDefinition> definitions = GetViewDefinitions(srcMode);
            switch (dstType)
            {
                case DbType.MySql:
                    OracleToMySqlView(definitions, targetId, dstDbName);
                    break;
                case DbType.SqlServer:
                    break;
                case DbType.Sqlite:
                    break;
                case DbType.Oracle:
                    break;
                case DbType.PostgreSQL:
                    break;
                case DbType.Dm:
                    break;
                default:
                    break;
            }
        }

        /// <summary>
        /// Oracle 视图 -> MySQL 视图
        /// </summary>
        private void OracleToMySqlView(List<ViewDefinition> definitions, string dstId, string dstDbName)
        {
            ConnectionConfig? connConfig = ConnectionConfigs.FirstOrDefault(c => c.ConfigId == dstId);
            if (connConfig == null)
                throw new Exception("指定的目标数据源 ID 未找到");

            SugarDbContext dstContext = DbFactory.CreateDbContext(DbType.MySql, connConfig);
            string template = "CREATE OR REPLACE VIEW `{0}`.`{1}` AS {2}";
            RegexOptions opts = RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.IgnoreCase;

            List<ViewDefinition> retry = new();
            for (int round = 1; round <= 2; round++)
            {
                foreach (var def in definitions)
                {
                    if (round == 1)
                    {
                        string sql = def.VIEW_DEFINITION.Replace("\"", "`").Replace("“", "");
                        foreach (var kv in OracleToMySqlFuncMapping)
                            sql = Regex.Replace(sql, kv.Key, kv.Value, opts, TimeSpan.FromSeconds(2));

                        def.VIEW_DEFINITION = string.Format(template, dstDbName, def.VIEW_NAME, sql);
                    }

                    bool ok = CreateView(dstContext, def.VIEW_NAME, def.VIEW_DEFINITION);
                    if (!ok)
                        retry.Add(def);
                    else
                        ConsoleLog.Success("RichText", $"视图 {def.VIEW_NAME} 创建成功.");
                }
                if (retry.Count == 0) break;
                definitions.Clear();
                definitions.AddRange(retry);
                retry.Clear();
            }

            // 输出失败日志
            foreach (var err in definitions)
            {
                ConsoleLog.Error("RichText", $"视图 {err.VIEW_NAME} 创建失败: {err.VIEW_DEFINITION}\n");
                string path = Path.Combine(Directory.GetCurrentDirectory(), "ViewLog");
                Directory.CreateDirectory(path);
                File.WriteAllText(Path.Combine(path, $"{err.VIEW_NAME}.log"), err.VIEW_DEFINITION);
            }
        }

        #endregion

        #region 数据迁移
        public override void DbMigrate(DbType targetType, string targetId, string dbName, string namesp = "Default.Models")
        {
            if (targetType == DbType.MySql)
                OracleToMySqlMigrate(ConfigId, targetId, dbName, namesp);
        }

        /// <summary>
        /// Oracle 数据 → MySQL 批量迁移
        /// </summary>
        private void OracleToMySqlMigrate(string sourceId, string targetId, string dbName, string namesp)
        {
            if (DynamicHelper.ModelAssembly == null)
            {
                ConsoleLog.Error("RichText", "实体类程序集未初始化");
                return;
            }
            if (ConnectionConfigs == null || ConnectionConfigs.Count == 0)
                throw new Exception("未初始化 ConnectionConfigs");

            // 源 Oracle 配置
            ConnectionConfig? srcCfg = ConnectionConfigs.FirstOrDefault(c => c.ConfigId == sourceId);
            ConnectionConfig? dstCfg = ConnectionConfigs.FirstOrDefault(c => c.ConfigId == targetId);
            if (srcCfg == null) throw new Exception("源数据源 ID 未找到");
            if (dstCfg == null) throw new Exception("目标数据源 ID 未找到");

            SqlSugarClient srcClient = new(srcCfg);
            string tblSql = $"SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER = '{dbName}'";
            List<string> tables = srcClient.Ado.SqlQuery<string>(tblSql);

            tableRowMap.Clear();
            foreach (var t in tables)
            {
                int cnt = srcClient.Ado.GetInt($"SELECT COUNT(*) FROM \"{t}\"");
                tableRowMap[t] = cnt;
                ConsoleLog.Info("RichText", $"表 {t}, 行数 {cnt}");
            }

            List<Action> tasks = new();
            Type sugarType = typeof(SqlSugarClient);

            foreach (var table in tableRowMap.Keys)
            {
                string typeName = $"{namesp}.{table}";
                Type? entityType = DynamicHelper.ModelAssembly.GetType(typeName);
                if (entityType == null)
                {
                    ConsoleLog.Error("RichText", $"未找到实体类型: {table}");
                    continue;
                }

                tasks.Add(() =>
                {
                    int total = QueryAndInsert(sourceId, targetId, sugarType, table, entityType);
                    ConsoleLog.Success("RichText", $"表 {table} 迁移完成，共 {total} 行");
                });
            }

            Parallel.Invoke(new ParallelOptions { CancellationToken = ct, MaxDegreeOfParallelism = 10 }, tasks.ToArray());
        }

        #endregion

        #region 类型转换
        private string OracleToCsharpTypeConvert(RowColumnInfo rowColumn)
        {
            if (string.IsNullOrWhiteSpace(rowColumn.DataType))
                return "object";

            // 统一大写，便于匹配
            string oracleType = rowColumn.DataType.Trim().ToUpperInvariant();

            switch (oracleType)
            {
                case "CHAR":
                case "NCHAR":
                case "VARCHAR":
                case "VARCHAR2":
                case "NVARCHAR2":
                case "CLOB":
                case "NCLOB":
                    return "string";
                case "NUMBER":
                case "NUMERIC":
                    if (rowColumn.Precision != null && rowColumn.Scale != null)
                    {
                        return "decimal";
                    }
                    return "int";
                case "FLOAT":
                    return "float";
                case "DECIMAL":
                    // NUMBER 具体映射还需结合精度，但通常泛型用 decimal 兼容性高
                    return "decimal";

                case "INT":
                case "INTEGER":
                case "SMALLINT":
                    return "int";

                case "BINARY_INTEGER":
                    return "int";

                case "BIGINT":
                    return "long";

                case "DATE":
                case "TIMESTAMP":
                case "TIMESTAMP WITH TIME ZONE":
                case "TIMESTAMP WITH LOCAL TIME ZONE":
                    return "DateTime";

                case "BLOB":
                case "RAW":
                case "LONG RAW":
                    return "byte[]";

                case "LONG":
                    return "string"; // Oracle LONG 实际为大文本类型

                case "BOOLEAN": // Oracle PL/SQL 中支持 BOOLEAN 类型（在某些上下文中）
                    return "bool";

                default:
                    return "object"; // 默认 fallback
            }
        }

        #endregion
    }

    internal class RowColumnInfo
    {
        public string TableName { get; set; }
        public string FileName { get; set; }
        public string TbComment { get; set; }
        public int ColumnId { get; set; }
        public string DbColumnName { get; set; }
        public string Default { get; set; }
        public string ColComment { get; set; }
        private string dataType;

        public string DataType
        {
            get { return dataType; }
            set { dataType = value; }
        }

        public int Length { get; set; }
        public int? Precision { get; set; }
        public int? Scale { get; set; }
        public string Nullable { get; set; }
        public string IsPrimaryKey { get; set; }
    }
}
